Homework 2 - ER Diagrams

Author

Dhilan Kamani

[Repository]

Project Overview

Using entity-relation diagrams (ERD), a plan can be laid out, detailing entities, their attributes, and their relationships to one another. Entities are essentially the real world objects that are being described/identified by attributes, for example, a gas heater (entity) has a serial number (attribute). A relationship that gas heater may have is to the AC/heating system, or to the water heater. This project provides real world examples of entity sets with varying relationships as practice to produce ERDs using both Chen and Crows foot diagram notations. Chen diagrams provide an explicit description of relationships while Crows foot diagrams are minimalistic, still providing the same information, just in different ways.

Real Estate Listing System

Imagine a real estate agency that wants to track properties, agents, and clients. Each property has a property ID, address, and listing price. Agents have an agent ID, name, and contact information. Clients have a client ID, name, and budget. An agent can handle multiple properties, but each property is listed by a single agent. Clients can be interested in multiple properties, and each property can have multiple interested clients.

Conceptual design in Chen Notation

Below is an entity-relation diagram in Chen notation for a real estate listing system. The diagram explores the relationships between agents, properties, and clients.

RELS Property Property P-A P-A Property--P-A 1 Agent Agent A-P A-P Agent--A-P 1 Client Client C-P C-P Client--C-P n pID ID pID--Property aID ID aID--Agent cID ID cID--Client aName Name aName--Agent cName Name cName--Client Address Address Address--Property Price Price Price--Property Phone Phone Phone--Agent Budget Budget Budget--Client A-P--Property n P-A--Agent 1 C-P--Property n
Figure 1: A Chen diagram for a real estate listing system

Conceptual design in Crows foot notation

Below is an entity relation diagram for the same real estate listing system as above, however this diagram utilizes Crow’s foot notation, providing simplified viewing experience.

erDiagram 
    
    PROPERTY {
        int pID
        int Price
        string Address
    }

    AGENT {
        int aID
        int Phone
        string name
    }

    CLIENT {
        int cID
        int Budget
        string Name
    }

    PROPERTY }o--|| AGENT : has
    PROPERTY }|--o{ CLIENT : has

Design Caveats

Assumptions: * Clients and agents are only related through a property * A client must be interested in at least one property * A property can have zero interested clients

Relation sets

The following are the initial relation sets for the system:

  • Agent(agent_ID, name, contact_information)
  • Property(property_ID, address, listing_price)
  • Client(client_ID, name, budget)

Restaurant Reservation System

Envision a restaurant that wants to manage reservations, customers, and tables. Each reservation has a reservation ID, date, and time. Customers have a customer ID, name, and contact number. Tables have a table number, seating capacity, and location. A customer can make multiple reservations, and each reservation is for a single table. Each table can have multiple reservations over time, but each reservation is associated with only one customer.

Conceptual design in Chen Notation

RRS Reservation Reservation resID ID Reservation--resID Date Date Reservation--Date Time Time Reservation--Time R-C R-C Reservation--R-C 1 R-T R-T Reservation--R-T 1 Customer Customer custID ID Customer--custID Name Name Customer--Name Phone Phone Customer--Phone C-R C-R Customer--C-R 1 Table Table Table_Num Table_Num Table--Table_Num Capacity Capacity Table--Capacity Location Location Table--Location T-R T-R Table--T-R 1 R-C--Customer 1 C-R--Reservation n T-R--Reservation many R-T--Table 1
Figure 2: A Chen diagram for a restaurant reservation system

Conceptual design in Crows foot notation

erDiagram

    CUSTOMER ||--|{ RESERVATION : has
    CUSTOMER{
        int custID
        int Phone
        string Name
    }
    
    RESERVATION }o--|| TABLE : has
    RESERVATION{
        int resID
        string Date
        string Time
    }

    TABLE{
        int Table_Num
        int Capacity
        string Location
    }

Design Caveats

  • Tables can have zero or one reservation at a time, with many over the course of a day
  • While tables may have a certain capacity, customers may request more seating than the capacity

Relation sets

The following are the initial relation sets for the system:

  • Reservation( reservation_ID, date, time )
  • Customer( customer_ID, name, contact_number )
  • Table( table_number, seating_capacity, location )

Sports Tournament Management System

Consider a sports tournament that wants to manage teams, matches, and referees. Each team has a team ID, name, and coach. Matches have a match ID, date, and location. Referees have a referee ID, name, and certification level. A team can participate in multiple matches, and each match involves two teams. A referee can officiate multiple matches, but each match is officiated by a single referee.

Conceptual design in Chen Notation

STMS Team Team tID ID Team--tID tName Name Team--tName Coach Coach Team--Coach T-M T-M Team--T-M 1 Match Match mID ID Match--mID Date Date Match--Date Location Location Match--Location M-T M-T Match--M-T 1 M-R M-R Match--M-R 1 Referee Referee rID ID Referee--rID rName Name Referee--rName Cert_Level Cert_Level Referee--Cert_Level R-M R-M Referee--R-M 1 T-M--Match n M-T--Team 2 M-R--Referee 1 R-M--Match many
Figure 3: A Chen diagram for a sports tournament management system

Conceptual design in Crows foot notation

erDiagram

    MATCH{
        int mID
        string Date
        string Location
    }

    TEAM }|--|{ MATCH : has
    TEAM{
        int tID
        string Name
        string Coach
    }

    REFEREE ||--o{ MATCH : has
    REFEREE{
        int rID
        int Cert_Level
        string Name
    }

Design Caveats

  • Although the Crow’s foot diagram denotes matches as having 1 to many teams, there are only 2 teams for each match
  • Every team must play in at least one match to be included in the system

Relation sets

The following are the initial relation sets for the system:

  • Teams( team_ID, name, coach )
  • Match( match_ID, date, location )
  • Referee( referee_ID, name, certification_level )

Reflection

What do you find most difficult about the assignment?
Finding where the syntax errors are. I’m used to writing code in a text editor that has a checkstyle which will highlight syntax errors, or in a Python editor, which will spit out line numbers for errors.
What do you find easiest about the assignment?
Learning the notation for Chen/Crow’s foot. Both are pretty intuitive, although Chen notation is highly repetitive, and while I didn’t make any errors writing the code for it on this assignment, I can tell that on larger sets of entitities, it could become a hassle and lead to minor typos that will make me want to pull my hair out when trying to find them.
What did you learn that will make the next assignment easier?
Well, I am unsure what the next assignment entails, however, having learned the design and implementation of ERDs, I assume this will help me organize and relate datasets for a larger project.